PL/SQL（Procedure Language & Structured Query Language ）

1.基本语法

declare
v_id employees.employee_id % type ; --动态获取表中字段的类型
v_email employees.email % type ;
v_salary employees.salary % type ;
begin
v_id := 105;
select e.email, e.salary

into v_email , v_salary
from employees e
where e.employee_id = v_id;

dbms_output.put_line( 'id:' || v_id || '， email:' || v_email ||'， salary:' || v_salary);
end;

2.记录类型

declare
type emp_rec is record(

v_name      employees.last_name %type,
v_salary    employees.salary %type,
v_hire_date date );

v_emp_rec emp_rec;
v_emp_id employees.employee_id%type ;
begin
v_emp_id := 105;
select e.last_name, e.salary , e.hire_date

into v_emp_rec
from employees e
where e.employee_id = v_emp_id;

dbms_output.put_line( 'id:' || v_emp_id || ', name:' || v_emp_rec.v_name ||

                   ', salary:' || v_emp_rec.v_salary || ', hiredate:' || v_emp_rec.v_hire_date);

end;

declare
v_emp_rec employees %rowtype ; --返回一个记录类型
v_emp_id employees.employee_id %type ;
begin
v_emp_id := 105;
select * into v_emp_rec from employees e where e.employee_id = v_emp_id;
dbms_output.put_line( 'id:' || v_emp_id || ', name:' ||

                   v_emp_rec.last_name || ', salary:' || v_emp_rec.salary);

end;

3.流程控制

--3.1选择结构
--3.1.1。if...then...;elsif...then...;else...;end if;
declare
v_id employees.employee_id%type ;
v_salary employees.salary%type ;
v_tmp varchar2(20 );
begin
v_id := 105;
select e.salary into v_salary from employees e where e.employee_id = v_id;
if v_salary >= 10000 then v_tmp := '大于10000' ;
elsif v_salary >= 5000 then v_tmp := '在5000到10000之间' ;
else v_tmp := '小于5000' ;
end if;
dbms_output.put_line( v_tmp);
end;
--3.1.2。case...when...then...else...end;
declare
v_id employees.employee_id%type ;
v_salary employees.employee_id%type ;
v_tmp varchar2(20 );
begin
v_id := 105;
select e.salary into v_salary from employees e where e.employee_id = v_id;
v_tmp := case trunc (v_salary / 5000)

 when 2 then '大于10000' when 1 then '在5000到10000之间' else '小于5000' end ;

dbms_output.put_line( v_tmp);
end;
--3.2循环结构
--3.2.1。loop...exit when... ...end loop;
declare
v_i number(8 );
begin
v_i := 1;
loop

dbms_output.put_line(v_i );

exit when v_i >= 100;

v_i := v_i + 1;

end loop;
end;
--3.2.2。while...loop... ...end loop;
declare
v_i number(8 );
begin
v_i := 1;
while v_i <= 100 loop

dbms_output.put_line(v_i );
v_i := v_i + 1;

end loop;
end;
--3.2.3。数字式循环
begin
for c in 1 .. 100 loop

dbms_output.put_line(c );

end loop;
end;
--求100以内的素数
--while循环实现
declare
v_i number(3 ); --用于遍历2-100
v_j number(2 ); --充当模数
v_flag number(1 ); --判断是否不是素数
begin
v_i := 1;
while v_i <= 100 loop

v_j    := 2 ;
v_flag := 0 ;

while v_j <= sqrt( v_i) and v_flag = 0 loop
  if mod (v_i, v_j) = 0 then v_flag := 1 ;
  end if ;
  v_j := v_j + 1;
end loop ;
if v_flag = 0 then dbms_output.put_line (v_i);
end if ;

v_i := v_i + 1;

end loop;
end;
--for循环实现
declare
v_flag number (1 );
begin
v_flag := 0;
for i in 2 .. 100 loop

for j in 2 .. sqrt( i ) loop
  if mod ( i, j ) = 0 then v_flag := 1 ;
    goto lable ; --使用goto提升效率
  end if ;
end loop ;
if v_flag = 0 then dbms_output.put_line ( i);
end if ;
<< lable >>
v_flag := 0 ;

end loop ;
end ;

4.游标使用

declare
v_name employees.last_name%type ;
v_sal employees.salary%type ;
Cursor v_emp_cur is

select e.last_name , e.salary from employees e where e.department_id = 80;

begin
open v_emp_cur;
fetch v_emp_cur into v_name, v_sal;
while v_emp_cur%found loop --v_sal_cur%found用于判断游标最近一次的读记录是否成功返回

dbms_output.put_line('name:' || v_name || ', sal:' || v_sal );
fetch v_emp_cur into v_name, v_sal ;

end loop;
close v_emp_cur;

end;

declare
type emp_rec is record(

v_name      employees.last_name %type,
v_sal       employees.salary% type,
v_hire_date date );

v_emp_rec emp_rec;
Cursor v_emp_cur is select last_name, salary , hire_date from employeeswhere department_id = 80 ;
begin
open v_emp_cur;
fetch v_emp_cur into v_emp_rec;
while v_emp_cur%found loop

dbms_output.put_line('name:' || v_emp_rec.v_name || ', sal:' ||
                     v_emp_rec.v_sal || ', hire_date:' || v_emp_rec.v_hire_date);
fetch v_emp_cur into v_emp_rec;

end loop;
close v_emp_cur;

end;

declare
Cursor v_emp_cur is select last_name, salary , hire_date from employeeswhere department_id = 80 ;
begin
for c in v_emp_cur loop

dbms_output.put_line('name:' || c.last_name || ', sal:' || c.salary ||
                     ', hire_date:' || c.hire_date );

end loop;
end;
--利用游标, 调整公司中员工的工资:
工资范围 调整基数
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
declare
v_tmp number(4 , 2);
Cursor v_emp_cur is select e.employee_id, e.salary from employees e ;
begin
for c in v_emp_cur loop

if c.salary >= 15000 then v_tmp := 0.01;
elsif c.salary >= 10000 then v_tmp := 0.02;
elsif c.salary >= 5000 then v_tmp := 0.03;
else v_tmp := 0.05;
end if ;

update employees set salary = c.salary * (1 + v_tmp) where employee_id = c.employee_id ;

end loop;

commit;
end;
--带参游标
declare
Cursor v_emp_cur (dept_id number, sal number ) is

select e.last_name , e.salary from employees e where e.department_id = dept_id and e.salary > sal ;

begin
for c in v_emp_cur( dept_id => 80 , sal => 6000 ) loop --通过=>符号向dept_id和sal赋值

dbms_output.put_line ('name:' || c.last_name || ', salary:' || c.salary );

end loop ;
end ;
--隐式游标
begin
update employees set salary = 10000 where employee_id = 1111 ;
if sql %notfound then dbms_output.put_line ('查无此人' ); --sql%notfound最近一次读记录时未成功返回
end if ;
end ;

5.异常处理

6.存储过程和存储函数的使用

create or replace function tst_func (i number, j number)
return date
is
v_date date;
begin
dbms_output.put_line( i + j );
v_date:= sysdate;
return v_date;
end;
执行方法①：
select tst_func (2, 3) from dual;
执行方法② ：
begin
dbms_output.put_line( tst_func(2 , 3));
end;
--获取指定部门的工资和该部门的人数
create or replace function sal_func (dept_id number, v_mbnum out number) --out型参数实现多个返回值
return number
is
v_sum_sal number(20 , 2);
Cursor sal_cur is select salary from employees where department_id = dept_id;
begin
v_sum_sal := 0;
v_mbnum := 0;
for c in sal_cur loop

v_mbnum   := v_mbnum + 1;
v_sum_sal := v_sum_sal + c.salary;

end loop;
return v_sum_sal;
end;
declare
v_sum_sal number(20 , 2);
v_mbnum number(5 );
v_dept_id number(4 );
begin
v_mbnum := 0;
v_dept_id := 90;
v_sum_sal := sal_func(v_dept_id , v_mbnum);
dbms_output.put_line( 'v_mbnum:' || v_mbnum || ', v_sum_sal:' || v_sum_sal);

end;

--使用存储过程实现上面的功能
create or replace procedure sal_proc (dept_id number, v_mbnum out number, v_sum_sal out number)
is
Cursor sal_cur is select salary from employees where department_id = dept_id;
begin
v_sum_sal := 0;
v_mbnum := 0;
for c in sal_cur loop

v_mbnum   := v_mbnum + 1;
v_sum_sal := v_sum_sal + c.salary;

end loop;

end;

declare
v_sum_sal number(20 , 2);
v_mbnum number(5 );
v_dept_id number(4 );
begin
v_mbnum := 0;
v_sum_sal := 0;
v_dept_id := 90;
sal_proc( v_dept_id, v_mbnum , v_sum_sal);
dbms_output.put_line( 'v_mbnum:' || v_mbnum || ', v_sum_sal:' || v_sum_sal);
end;
--实现指定部门在1995年前进公司的员工加薪5%,1998-1995加薪3%,1998年之后加薪5%并返回加薪成本
create or replace procedure sal_proc ( dept_id number, v_cost out number)
is
v_rate number(4 , 2);
Cursor sal_cur is select employee_id, salary , hire_date from employees where department_id = dept_id ;
begin
v_cost:= 0;

for c in sal_cur loop

  if to_char (c.hire_date, 'yyyy') < '1995' then v_rate:=0.05 ;
  elsif to_char (c.hire_date, 'yyyy') < '1998' then v_rate:=0.03 ;
  else v_rate :=0.01;
  end if ;
 
  update employees set salary = c.salary * (1 + v_rate) where employee_id = c.employee_id ;
  v_cost:= v_cost + c.salary * v_rate;

end loop;

end;

declare
v_dept_id number(5 );
v_cost number (10 , 2);
begin
v_dept_id:= 90;
v_cost:= 0;
sal_proc( v_dept_id, v_cost );

dbms_output.put_line( v_dept_id||'此次提薪成本：' ||v_cost);
end;

7.触发器

create or replace trigger emp_tr
before delete
on employees --[after|before] [update|delete|insert] on ...
for each row
when ( sysdate < to_date ('2222', 'yyyy'))
begin
insert into emp_bak values(: old.employee_id, :old.salary ); --删除前对数据进行备份
dbms_output.put_line( 'old sal:' || :old.salary || ', new sal:' || :new.salary); --用:new.、:old.获得修改前、后的值
end;

==========================================================================
mysql存储过程
DELIMITER $
CREATE PROCEDURE mybatis.get_meb_cot(IN gd INT, OUT meb_count INT)
BEGIN

 SELECT COUNT(id) FROM members WHERE gender = gd INTO meb_count;

END
$
DROP PROCEDURE get_meb_cot;
SET @count = 0;
CALL mybatis.get_meb_cot(0, @count);
SELECT @count;